var utility = require('system/utility'),
	DataSet = require('dataAccess/dataset'),
	Connection = require('tds'),
	db = null;

function log() {
	utility.log(__filename,arguments);	
}

function DataRow(tdsRecord) {
	var me = this;
	//log("New dataRow", tdsRecord);
	me.tdsRecord = tdsRecord;
	utility.extend(me,{
		eachColumn: function(callback) {
			var c, cc,v;
			try {
				for (c in me.tdsRecord.metadata.columns) {
					cc = me.tdsRecord.metadata.columns[c];
					try {
						v = me.tdsRecord.getValue(cc.index);
					} catch (e) {
						v = "";
						log("oops (DataRow.each.getValue): ",cc.name,", ",e);
					}
					try {
						callback(cc,v);
					} catch (e) {
						log("oops (DataRow.each.callback): ",cc.name,", ",e);
					}
				}
			} catch (e) {
				log("tds.eachColumn")
			}
		},
		asHTML: function() {
			var str = "";
			me.eachColumn(function(c,v){
				str += "<td>" + v + "</td>";
			});
			return str.length ? "<tr>" + str + "</tr>\n" : "";
		},
		asObject: function() {
			var obj = {};
			me.eachColumn(function(c,v){
				obj[c.name] = v;
			});
			return obj;
		}
	});
}

function openDevDB(callback) {
	log("instantiating connection")
	db = new Connection.Connection({
			host: 'NRCDCADEVDB01',
			port: 1433,
			userName: 'developer',
			password: 'developer',
			handler: {
				error: function(error) {
					log("DB error: " + error);
				},
				message: function(msg) {
					log("DB message: " + msg);
				}
			}
		});
	try {
		db.connect(callback);
	} catch (e) {
		callback("Oops: database connection failed: " + e.message);
	}
}

function closeDB() {
	if (db) {
		db = null;
	}
}

function doQuery(rr, callback, query) {
	function done() {
		log("--- sql Callback")
		if (callback)
			callback(rr);
	}
	var sql, rows = [], str = "", ds, rowCount = 0;
	openDevDB(function(error){
		if (error != null) {
			console.error('db connect error: ', error);
			done();
		} else {
			try {
				ds = new DataSet.DataSet();
				sql = db.createStatement(query, null, {
					row: function (r) {
						log("--- sql ROW (" + (++rowCount) + ") ---");
						ds.addRow(r);
					},
					error: function(err) {
						str = JSON.stringify({ success: false, errors: [ err ] });
						rr.response.write(str);
						log("--- sql ERROR: ", str);
						//done();
					},
					done: function () {
						str = JSON.stringify({ success: true, data: ds.asObject()});
						log("--- sql DONE: ", str);
						rr.response.write(str);
						done();
					}
				});
				log("executing query: " +  query);
				sql.execute();
			} catch (e) {
				log("(TDS) SQL exception: ", e, ds)
			}
		}
	});
}

exports.close = closeDB;
exports.query = doQuery;
exports.DataRow = DataRow;
